﻿using System;
using System.Collections.Generic;
using System.Text;
using LHS.Core.TableInfo;
using System.Data.Common;
using System.Collections.Specialized;
using System.Data;
using MS.Core.Utils;
using MS.Core.Constants.Objects;

namespace LHS.Core.Clinical
{
    public class CommonClinical : MS.Core.Base.Data.DBBaseData
    {
        /// <summary>
        /// lấy ra mã số mới nhất của bảng ClinicalRecord
        /// </summary>
        /// <returns></returns>
        public string GetNewClrISN()
        {
            try
            {
                object iClrISN = base.LoadFromSqlScalar("select max(" + InfoClinicalRecord.CLRISN + ") + 1 from " + InfoClinicalRecord.TABLENAME);
                return iClrISN.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// lấy ra mã số mới nhất của bảng Patients
        /// </summary>
        /// <returns></returns>
        public string GetPATID()
        {
            try
            {
                object iPatID = base.LoadFromSqlScalar("select " + InfoPatients.PATID + " + 1 from " + InfoPatients.TABLENAME + " where " + InfoPatients.PATISN + " = (select max(" + InfoPatients.PATISN + ") from " + InfoPatients.TABLENAME + ")");
                return iPatID.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// insert ClinicalRecord theo store sp_Patients_ClinicalRecordNurse_Insert
        /// </summary>
        /// <param name="param"></param>
        public void InsertClinicalRecord(ListDictionary param)
        {
            try
            {
                base.LoadFromSqlNoExec("sp_Patients_ClinicalRecordNurse_Insert", param, CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// lấy ra cây triệu chứng
        /// </summary>
        /// <returns></returns>
        public DataTable GetSickTree()
        {
            try
            {
                DbCommand cmd = base.GetDbCommand(System.Data.CommandType.Text, "select distinct(" + InfoSickTree.SKTISN + "), " + InfoSickTree.SKTNAME + " from vw_SickTree_Sicks order by " + InfoSickTree.SKTNAME);
                return base.GetData(cmd, InfoSickTree.TABLENAME);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy ra thông tin bệnh nhân có cấp thuốc
        /// </summary>
        /// <param name="strPatID"></param>
        /// <param name="strPatName"></param>
        /// <returns></returns>
        public DataTable GetPatientHasPrescriptionDT(string strPatID, string strPatName)
        {
            try
            {
                StringBuilder sql = new StringBuilder("Select " + InfoClinicalRecord.CLRISN + "," + InfoPatients.PATID + ", " + InfoPatients.PATNAME +
                        "," + InfoPatients.PATBIRTHDATE + "," + InfoClinicalRecord.CLREXAMINATIONDATE + ",clrExaminationDateVN," + InfoClinicalRecord.CLREXAMINATIONTIMES +
                        "," + InfoClinicalRecord.CLRREXAMINATIONTIMES + " from  vw_PatientHasPrescription  ");

                //chỉ có thể có 1 trong 2 trường hợp xảy ra
                //đang sử dụng trong form BANotCapThuoc
                ListDictionary param = new ListDictionary();
                if (Checker.CheckBlankValue(strPatID, MS.Core.Constants.GlobalEnumType.DataType.String) == false)
                {
                    sql.Append(" where " + InfoPatients.PATID + " = @patID");
                    param.Add(InfoPatients.PATID, strPatID);
                }
                if (Checker.CheckBlankValue(strPatName, MS.Core.Constants.GlobalEnumType.DataType.String) == false)
                {
                    sql.Append(" where " + InfoPatients.PATNAME + " like @patName");
                    param.Add(InfoPatients.PATNAME, "%" + strPatName + "%");
                }
                DbCommand cmd = base.GetDbCommand(CommandType.Text, sql.ToString(), param);
                return base.GetData(cmd, "vw_PatientHasPrescription");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        ///  su dung cho form BAO CAO CUOI NGAY
        /// </summary>
        /// <param name="blnCheckAll">neu truyền check all bằng true, thì sẽ lọc theo nhân viên</param>
        /// <param name="strDateFrm"></param>
        /// <param name="strDateTo"></param>
        /// <returns></returns>
        public DataTable GetClinicalInvoices(bool blnCheckAllEmpISN, string strDateFrm, string strDateTo, string strEmpISN)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(" select distinct * from vw_ClinicalInvoices where ((" + InfoInvoices.INVEXAMINATIONPAYMENT + " IS not NULL ) and (" + InfoInvoices.INVMEDICINEPAYMENT + " IS not NULL ) and (" + InfoInvoices.INVMEDICINESKILLPAYMENT + " IS not null ) and (" + InfoInvoices.INVSURGERYPAYMENT + " IS not NULL ))");

                List<ExpressionObject> param = new List<ExpressionObject>();
                param.Add(new ExpressionObject(InfoClinicalRecord.CLREXAMINATIONDATE, strDateFrm + " 00:00:00 AM", strDateTo + " 00:00:00 AM", ExpressionObject.eSelectOperator.Between, ExpressionObject.eCombineOperator.And));
                if (blnCheckAllEmpISN == false)
                {
                    param.Add(new ExpressionObject(InfoEmployees.EMPISN, strEmpISN));
                }
                return base.GetData("vw_ClinicalInvoices", sql.ToString(), param, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        ///  su dung cho form BAO CAO CUOI NGAY
        /// </summary>
        /// <param name="blnCheckAllSick"></param>
        /// <param name="blnCheckAllEmpISN"></param>
        /// <param name="strDateFrm"></param>
        /// <param name="strDateTo"></param>
        /// <param name="strEmpISN"></param>
        /// <param name="strPltISN"></param>
        /// <returns></returns>
        public DataTable GetPathologicalClinical(bool blnCheckAllSick, bool blnCheckAllEmpISN, string strDateFrm, string strDateTo, string strEmpISN, string strPltISN)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(" select distinct * from vw_PathologicalClinical");
                List<ExpressionObject> param = new List<ExpressionObject>();
                if (blnCheckAllSick && !blnCheckAllEmpISN)
                {
                    sql.Append(" where " + InfoPathologicalTree.PLTNAME + " is not null");
                    param.Add(new ExpressionObject(InfoClinicalRecord.CLREXAMINATIONDATE, strDateFrm + " 00:00:00 AM", strDateTo + " 00:00:00 AM", ExpressionObject.eSelectOperator.Between, ExpressionObject.eCombineOperator.And));
                    param.Add(new ExpressionObject(InfoEmployees.EMPISN, strEmpISN));
                }
                else if (blnCheckAllSick && blnCheckAllEmpISN)
                {
                    sql.Append(" where " + InfoPathologicalTree.PLTNAME + " is not null");
                    param.Add(new ExpressionObject(InfoClinicalRecord.CLREXAMINATIONDATE, strDateFrm + " 00:00:00 AM", strDateTo + " 00:00:00 AM", ExpressionObject.eSelectOperator.Between, ExpressionObject.eCombineOperator.And));

                }
                else if (!blnCheckAllSick && !blnCheckAllEmpISN)
                {
                    sql.Append(" where 1=1 ");
                    param.Add(new ExpressionObject(InfoClinicalRecord.CLREXAMINATIONDATE, strDateFrm + " 00:00:00 AM", strDateTo + " 00:00:00 AM", ExpressionObject.eSelectOperator.Between, ExpressionObject.eCombineOperator.And));
                    param.Add(new ExpressionObject(InfoEmployees.EMPISN, strEmpISN));
                    param.Add(new ExpressionObject(InfoPathologicalTree.PLTISN, strPltISN));
                }
                else
                {
                    sql.Append(" where 1=1 ");
                    param.Add(new ExpressionObject(InfoClinicalRecord.CLREXAMINATIONDATE, strDateFrm + " 00:00:00 AM", strDateTo + " 00:00:00 AM", ExpressionObject.eSelectOperator.Between, ExpressionObject.eCombineOperator.And));
                    param.Add(new ExpressionObject(InfoPathologicalTree.PLTISN, strPltISN));

                }
                return base.GetData("vw_PathologicalClinical", sql.ToString(), param, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        ///  su dung cho form BAO CAO CUOI NGAY
        /// </summary>
        /// <param name="blnCheckAllEmpISN"></param>
        /// <param name="strDateFrm"></param>
        /// <param name="strDateTo"></param>
        /// <param name="strEmpISN"></param>
        /// <returns></returns>
        public DataTable GetPathologicalSignsOutList(bool blnCheckAllEmpISN, string strDateFrm, string strDateTo, string strEmpISN)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(" select distinct * from vw_PathologicalClinical");

                List<ExpressionObject> param = new List<ExpressionObject>();
                sql.Append(" where " + InfoPathologicalOutList.POLPATHOLOGICALSIGNSOUTLIST + " is not null and " + InfoPathologicalOutList.POLPATHOLOGICALSIGNSOUTLIST + " <> ''");
                param.Add(new ExpressionObject(InfoClinicalRecord.CLREXAMINATIONDATE, strDateFrm + " 00:00:00 AM", strDateTo + " 00:00:00 AM", ExpressionObject.eSelectOperator.Between, ExpressionObject.eCombineOperator.And));

                if (!blnCheckAllEmpISN)
                {
                    param.Add(new ExpressionObject(InfoEmployees.EMPISN, strEmpISN));
                }

                return base.GetData("vw_PathologicalClinical", sql.ToString(), param, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Su dung trong form DiagnoseSyptom
        /// </summary>
        /// <returns></returns>
        public DataTable GetInnerScan()
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(" select distinct " + InfoInnerScan.NSCISN + " , " + InfoInnerScan.NSCNAME + " from vw_InnerScan" +
                           " Union Select distinct nscISNChild ,  nscNameChild  from dbo.vw_InnerScan Where " + InfoInnerScan.NSCISN + " = 2 or  " + InfoInnerScan.NSCISN + " = 1");

                return base.GetData("InnerScan", sql.ToString(), null, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// su dung trong form HO SO BENH AN
        /// lay ra ma benh an truoc lan tai kham va lan kham truyen vao
        /// </summary>
        /// <param name="strID">id cua benh nhan</param>
        /// <param name="num">lan kham</param>
        /// <param name="num2">lan tai kham</param>
        /// <returns></returns>
        public DataTable Getfn_MaxRexamTimesOFMaxExamTimes(string strID, string num, string num2)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT * from dbo.fn_MaxRexamTimesOFMaxExamTimes (" + strID + "," + num + "," + num2 + ")");

                return base.GetData("MaxPrescription", sql.ToString(), null, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// lay ra lan tai kham moi nhat cua lan kham truyen vao, khong join voi bang medicineExport
        /// su dung trong form HO SO BENH AN
        /// </summary>
        /// <param name="strID"></param>
        /// <param name="num"></param>
        /// <param name="num2"></param>
        /// <returns></returns>
        public DataTable Getfn_MaxRexamTimesOFMaxExamTimesNoJoinMedExport(string strID, string num, string num2)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT * from dbo.fn_MaxRexamTimesOFMaxExamTimesNoJoinMedExport (" + strID + "," + num + "," + num2 + ")");

                return base.GetData(InfoClinicalRecord.TABLENAME, sql.ToString(), null, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// lay ra thong tin phau thuat tu chuoi phau thuat duoc chon truoc do
        /// </summary>
        /// <param name="strPhauThuat"></param>
        /// <returns></returns>
        public DataTable getSurgical(string strPhauThuat)
        {
            try
            {
                ListDictionary param = new ListDictionary();
                param.Add("@strSurgical", strPhauThuat);
                DataSet ds = base.LoadFromSql("usp_getSurgical", param, CommandType.StoredProcedure);
                DataTable dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// lay ra ngay kham
        /// </summary>
        /// <param name="strPatID"></param>
        /// <param name="intLanKham"></param>
        /// <param name="intLanTaiKham"></param>
        /// <returns></returns>
        public DataTable getExaminationDate(string strPatID, int intLanKham, int intLanTaiKham)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT " + InfoClinicalRecord.CLREXAMINATIONDATE + " from " + InfoClinicalRecord.TABLENAME + " inner join " + InfoPatients.TABLENAME);
                sql.Append(" on " + InfoClinicalRecord.TABLENAME + "." + InfoClinicalRecord.PATISN + " = " + InfoPatients.TABLENAME + "." + InfoPatients.PATISN + " where 1=1 ");

                List<ExpressionObject> paramGetDate = new List<ExpressionObject>();
                paramGetDate.Add(new ExpressionObject(InfoPatients.PATID, strPatID));
                paramGetDate.Add(new ExpressionObject(InfoClinicalRecord.CLREXAMINATIONTIMES, intLanKham));
                paramGetDate.Add(new ExpressionObject(InfoClinicalRecord.CLRREXAMINATIONTIMES, intLanTaiKham));

                return base.GetData(InfoClinicalRecord.TABLENAME, sql.ToString(), paramGetDate, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// lay ra toan bo hinh anh cua benh nhan
        /// </summary>
        /// <param name="strPatID"></param>
        /// <returns></returns>
        public DataTable getPicture(string strPatID)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT " + InfoClinicalPicture.TABLENAME + ".*," + InfoSurgical.TABLENAME + "." + InfoSurgical.SURNAME + " as " + InfoClinicalPicture.HIDDEN_SURNAME + " from " + InfoClinicalPicture.TABLENAME + " left join " + InfoSurgical.TABLENAME);
                sql.Append(" on " + InfoClinicalPicture.TABLENAME + "." + InfoClinicalPicture.SURISN + " = " + InfoSurgical.TABLENAME + "." + InfoSurgical.SURISN + " where 1=1 ");

                List<ExpressionObject> param = new List<ExpressionObject>();
                param.Add(new ExpressionObject(InfoClinicalPicture.PATID, strPatID));

                return base.GetData(InfoClinicalPicture.TABLENAME, sql.ToString(), param, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// lay ra ngay kham
        /// </summary>
        /// <param name="strPatID"></param>
        /// <param name="intLanKham"></param>
        /// <param name="intLanTaiKham"></param>
        /// <returns></returns>
        public DataTable getSurgicalInfo(string strPatID, int intLanKham,int intLanTaiKham)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT " + InfoClinicalRecord.CLRAPPOINTMENTDATE + "," + InfoClinicalRecord.CLRSURGERY + " from " + InfoClinicalRecord.TABLENAME + " inner join " + InfoPatients.TABLENAME);
                sql.Append(" on " + InfoClinicalRecord.TABLENAME + "." + InfoClinicalRecord.PATISN + " = " + InfoPatients.TABLENAME + "." + InfoPatients.PATISN + " where 1=1 ");

                List<ExpressionObject> paramGetDate = new List<ExpressionObject>();
                paramGetDate.Add(new ExpressionObject(InfoPatients.PATID, strPatID));
                paramGetDate.Add(new ExpressionObject(InfoClinicalRecord.CLREXAMINATIONTIMES, intLanKham));
                paramGetDate.Add(new ExpressionObject(InfoClinicalRecord.CLRREXAMINATIONTIMES, intLanTaiKham));
                return base.GetData(InfoClinicalRecord.TABLENAME, sql.ToString(), paramGetDate, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// lay ra lan tai kham co phau thuat dau tien cua benh nhan
        /// </summary>
        /// <param name="strPatID"></param>
        /// <param name="intLanKham"></param>
        /// <returns></returns>
        public int getLanTaiKhamHasPT(string strPatID, int intLanKham)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT top 1 " + InfoClinicalRecord.CLRREXAMINATIONTIMES + " from " + InfoClinicalRecord.TABLENAME + " inner join " + InfoPatients.TABLENAME);
                sql.Append(" on " + InfoClinicalRecord.TABLENAME + "." + InfoClinicalRecord.PATISN + " = " + InfoPatients.TABLENAME + "." + InfoPatients.PATISN + " where 1=1 ");
                sql.Append("AND (" + InfoClinicalRecord.CLRSURGERY + " IS NOT NULL and " + InfoClinicalRecord.CLRSURGERY + " <> '')");

                List<ExpressionObject> paramGetDate = new List<ExpressionObject>();
                paramGetDate.Add(new ExpressionObject(InfoPatients.PATID, strPatID));
                paramGetDate.Add(new ExpressionObject(InfoClinicalRecord.CLREXAMINATIONTIMES, intLanKham));

                ListDictionary orderby = new ListDictionary();
                orderby.Add(InfoClinicalRecord.CLRREXAMINATIONTIMES," asc ");
                DataTable dt = base.GetData(InfoClinicalRecord.TABLENAME, sql.ToString(), paramGetDate, orderby);
                if (dt == null || dt.Rows.Count == 0 )
                {
                    return 0;
                }
                else if (dt.Rows[0][InfoClinicalRecord.CLRREXAMINATIONTIMES] is int)
                {
                    return int.Parse(dt.Rows[0][InfoClinicalRecord.CLRREXAMINATIONTIMES].ToString());
                }
                else
                    return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// get ra key moi nhat cua clrPIC
        /// </summary>
        /// <param name="clrISN"></param>
        /// <returns></returns>
        public int GetNewCLRPIC(string clrISN)
        {
            try
            {
                ListDictionary param = new ListDictionary();
                param.Add(InfoClinicalPicture.CLRISN,clrISN);
                object iClPISN = base.GetScalar("isnull(max(" + InfoClinicalPicture.CLPISN + "),0) + 1", param,InfoClinicalPicture.TABLENAME);
                return iClPISN is int ? int.Parse(iClPISN.ToString()) : 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
